package muslim30;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.json.simple.JSONArray;

public class PrmDBMgr {
	//private DBConnectionMgr pool = null;
	private String _driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
	private String _url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=btjcenter";
	private String _user = "intercp21";
	private String _password = "gbs1040";
	private Connection con = null;
	
	public PrmDBMgr() {
		try{
			DriverManager.registerDriver((Driver) Class.forName(_driver).newInstance());

			Properties props = new Properties();
			props.put("user", _user);
			props.put("password", _password);

			con = DriverManager.getConnection(_url, props);	
        } catch(Exception e){
            System.out.println("Error : Fail to Get Connection!!");
        }
	}
	
	public List<Hashtable<String, String>> loginCheck(String id, String pass) throws SQLException, Exception {
		Statement stmt = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
		StringBuilder query = new StringBuilder();
		
		
		List<Hashtable<String,String>> list;
		
		try {
			stmt = con.createStatement();
			query.append("select * from member where memcode = '"+id+"'");
			rs = stmt.executeQuery(query.toString());
			rsmd = rs.getMetaData();
			
			int cc = rsmd.getColumnCount();
			
			list = new LinkedList<Hashtable<String,String>>();
			
			while(rs.next()) {
				Hashtable<String, String> h = new Hashtable<String, String>();
				
				for(int i = 1; i < cc ; i++) {
					h.put(rsmd.getColumnName(i), rs.getString(rsmd.getColumnName(i))!=null ? rs.getString(rsmd.getColumnName(i)) : "");
				}
				
				list.add(h);
			}
			
			return list;
		} catch (SQLException e) {
			throw e;
			//return LoginState.ID_NOT_FOUND;
		} finally {
			rs.close();
			stmt.close();
			con.close();
        }
	}
	
	public String getContentsAsJSON(String sql){
		
        Statement stmt = null;
        ResultSet rs = null;
        //StringBuffer strQuery = new StringBuffer();
		
		List<Map<String, String>> result = new LinkedList<Map<String,String>>();
		
		try {
            stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs != null && rs.next()) {
				Map<String, String> resultMap = new HashMap<String, String>();
				
				String num = rs.getString("num");
				String subject = rs.getString("subject");
				String regdate = rs.getString("regdate");
				String content = rs.getString("content");
				String filename1 = rs.getString("filename1");
				String filetype1 = rs.getString("filetype1");
				
				resultMap.put("num", num);
				resultMap.put("subject", subject);
				resultMap.put("regdate", regdate);
				resultMap.put("content", content);
				resultMap.put("filename1", filename1);
				resultMap.put("filetype1", filetype1);
				
				result.add(resultMap);
			}
		} catch(Exception e) {
			//e.printStackTrace();
			//String resultAsJSON = "[]";
			return "Exception: " + e.toString();
		} finally {
			try {
				if(rs != null)
					rs.close();
				stmt.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				return e.toString();
			}
        }
		String resultAsJSON = JSONArray.toJSONString(result);
		
		if(resultAsJSON.equals("null"))
			resultAsJSON = "[]";
		
		return resultAsJSON;
	}
	
	public String getContentsAsJSONFromRamadanBBSNation(String year, String nationName) {
		
		//String sql = "select * from ramadan_bbs_nation where year='"+year+"', nation='"+nationName+"'";
		
		StringBuffer strQuery = new StringBuffer();
		
		//num ,name,email,subject,regdate,filename1,filetype1,count,memcode,pos,depth,replyf,content
		strQuery.append(" select * from ramadan_bbs_nation a ");
        strQuery.append(" where year='"+year+"' and nation='"+nationName+"' ");
		
		try {
			return getContentsAsJSON(strQuery.toString());
		} catch (Exception e) {
			return "Exception:" + e.toString();
		}
	}
	
	public String getContentsAsJSONFromArticle() {
		try {
			return getContentsAsJSON("select * from ramadan_prayer_news");
		} catch (Exception e) {
			return "Exception:" + e.toString();
		}
	}
	
	public int insertLogHistory(String userid,String pwd_yn,String ip) throws SQLException,Exception{
        PreparedStatement pstmt = null;
        int ResultType = 0;
        String strQuery = "";
        //RSACryptor rsa = new RSACryptor();
        try {
            strQuery = "insert into LOGIN_LOG ( userid ,PWD_YN ,IPADDR ,R_DATE)  VALUES (?,?,?,getdate())";
            pstmt = con.prepareStatement(strQuery);
            pstmt.setString(1,userid);
            pstmt.setString(2,pwd_yn);
            pstmt.setString(3,ip);

            ResultType = pstmt.executeUpdate();
        }catch(Exception ex) {
            ex.printStackTrace();
            throw new Exception(ex.getMessage());
        }finally{
            pstmt.close();
            con.close();
        }
        return ResultType;
    }
}